﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.IO;

namespace MyBusiness.Common
{
    public class CommonPageOperation
    {
        Database dbGloab = BasicOperate.CreateDataBase();

        /// <summary>
        /// 执行sql并将返回信息转化为json字符串
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public string ExecuteToJson(string strSql)
        {
            DataTable dtSource = dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
            string strReturn = "";
            if (dtSource.Rows.Count > 0)
            {
                DataToJson _djson = new DataToJson();
                strReturn = _djson.BindJson(dtSource);
            }
            return strReturn;
        }

        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="tabname"></param>
        /// <param name="tabkeyname"></param>
        /// <param name="tabkeyvalue"></param>
        /// <returns></returns>
        public string GetTableInfo(string tabname, string tabkeyname, string tabkeyvalue)
        {
            string strSql = string.Format("select * from {0} where {1}={2}", tabname, tabkeyname, tabkeyvalue);
            return ExecuteToJson(strSql);
        }

        /// <summary>
        /// 判断数据表架构中指定的列是否是特殊字段类型，其中特殊类型明采用大写
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="strColumnName"></param>
        /// <param name="strType"></param>
        /// <returns></returns>
        public bool IsSpecialColumnType(DataTable dtSource,string strColumnName, string strType)
        {
            dtSource.DefaultView.RowFilter = string.Format(" ColumnName='{0}'",strColumnName);
            DataView dvTemp = dtSource.DefaultView;

            bool bReturn = false;
            if (dvTemp.Count > 0)
            {
                DataTable dtTemp = dvTemp.ToTable();
                if (dtTemp.Rows[0]["DataType"].ToString() == strType)
                {
                    bReturn = true;
                }
            }
            return bReturn;

        }

        /// <summary>
        /// 保存消息
        /// </summary>
        /// <param name="strParams">参数值</param>
        /// <returns></returns>
        public string SaveInfo(string tabname, string tabkeyname, string tabkeyvalue, object[] tabledata)
        {
            string strReturn = "";
            if (tabledata.Length > 0)
            {
                string strSql = "";
                Dictionary<string, object> dicList = tabledata[0] as Dictionary<string, object>;
                DataTable dtAllFields = GetFieldsNameOfTable(tabname);

                if (string.IsNullOrEmpty(tabkeyvalue) || tabkeyvalue == "null" || tabkeyvalue == "undefined")//插入
                {
                    string strFieldNames = string.Empty;
                    string strFieldValues = string.Empty;
                    foreach (string strKey in dicList.Keys)
                    {
                        strFieldNames += strKey + ",";

                        if (IsSpecialColumnType(dtAllFields,strKey,"System.DateTime"))//表示当前字段属于日期类型
                        {
                            strFieldValues += string.Format("CDate('{0}'),", dicList[strKey]);
                        }
                        else
                        {
                            strFieldValues += "'"+dicList[strKey].ToString() + "',";
                        }
                    }
                    strFieldNames = strFieldNames.Substring(0, strFieldNames.Length - 1);
                    strFieldValues = strFieldValues.Substring(0, strFieldValues.Length - 1);

                    strSql = string.Format("insert into {0}({1}) values({2})", tabname, strFieldNames, strFieldValues);
                }
                else
                {//修改

                    string strSets = string.Empty;
                    foreach (string strKey in dicList.Keys)
                    {
                        if (IsSpecialColumnType(dtAllFields, strKey, "System.DateTime"))//表示当前字段属于日期类型
                        {
                            strSets += string.Format("{0}=CDate('{1}'),", strKey,dicList[strKey]);
                        }
                        else
                        {
                            strSets += string.Format("{0}='{1}',", strKey, dicList[strKey]);
                        }
                    }
                    strSets = strSets.Substring(0, strSets.Length - 1);

                    //针对access的主键为长整形
                    strSql = string.Format("update {0} set {1} where {2}={3}", tabname, strSets, tabkeyname, tabkeyvalue);
                }
                dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
                strReturn = "修改成功";
            }

            return strReturn;
        }

        #region 获取指定表的所有列名及数据类型
        /// <summary>
        /// 获取指定表的所有列名及数据类型
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns></returns>
        public DataTable GetFieldsNameOfTable(string strTableName)
        {
            string strSql = string.Format("select * from {0}",strTableName);

            return dbGloab.ExecuteReader(CommandType.Text, strSql).GetSchemaTable();
        }
        #endregion

    }
}
